#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库管理模块
"""

import sqlite3
import json
from typing import Dict, List, Optional
from datetime import datetime


class DatabaseManager:
    """数据库管理类 - 支持按日期分表存储"""
    
    def __init__(self, db_path: str = "orders.db"):
        self.db_path = db_path
        self.current_date = datetime.now().strftime("%Y%m%d")
        self.init_database()
    
    def get_table_name(self, date: str = None) -> str:
        """获取指定日期的表名"""
        if date is None:
            date = self.current_date
        return f"orders_{date}"
    
    def init_database(self):
        """初始化数据库"""
        with sqlite3.connect(self.db_path) as conn:
            # 创建今天的表
            self.create_daily_table(self.current_date)
            # 创建客户表
            self.create_customers_table()
            # 迁移现有表，添加新字段
            self.migrate_existing_tables()
            conn.commit()
    
    def create_daily_table(self, date: str):
        """创建指定日期的订单表"""
        table_name = self.get_table_name(date)
        with sqlite3.connect(self.db_path) as conn:
            conn.execute(f"""
                CREATE TABLE IF NOT EXISTS {table_name} (
                    number TEXT PRIMARY KEY,
                    customer TEXT NOT NULL,
                    priority TEXT DEFAULT '中级',
                    notes TEXT DEFAULT '',
                    is_pending_engraving BOOLEAN DEFAULT FALSE,
                    color TEXT DEFAULT '单黑',
                    paper_direction TEXT DEFAULT '上下',
                    delivery_date TEXT DEFAULT '当天',
                    order_taker TEXT DEFAULT '',
                    designer TEXT DEFAULT '',
                    proofreader TEXT DEFAULT '',
                    proofreading_time TEXT DEFAULT '',
                    path TEXT DEFAULT '',
                    file_type TEXT DEFAULT 'PDF',
                    is_completed BOOLEAN DEFAULT FALSE,
                    not_engraved BOOLEAN DEFAULT FALSE,
                    solid_state BOOLEAN DEFAULT FALSE,
                    not_confirmed BOOLEAN DEFAULT FALSE,
                    pending BOOLEAN DEFAULT FALSE,
                    done BOOLEAN DEFAULT FALSE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            conn.commit()
            print(f"✅ 创建日期表: {table_name}")
    
    def migrate_existing_tables(self):
        """迁移现有表，添加新字段"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 获取所有以orders_开头的表
            cursor.execute("""
                SELECT name FROM sqlite_master 
                WHERE type='table' AND name LIKE 'orders_%'
            """)
            
            tables = cursor.fetchall()
            
            for table in tables:
                table_name = table[0]
                print(f"🔄 检查表 {table_name} 是否需要迁移...")
                
                # 检查表是否包含新字段
                cursor.execute(f"PRAGMA table_info({table_name})")
                columns = [col[1] for col in cursor.fetchall()]
                
                # 添加缺失的字段
                if 'color' not in columns:
                    print(f"➕ 为表 {table_name} 添加 color 字段")
                    cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN color TEXT DEFAULT '单黑'")
                
                if 'paper_direction' not in columns:
                    print(f"➕ 为表 {table_name} 添加 paper_direction 字段")
                    cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN paper_direction TEXT DEFAULT '上下'")
                
                if 'file_type' not in columns:
                    print(f"➕ 为表 {table_name} 添加 file_type 字段")
                    cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN file_type TEXT DEFAULT 'PDF'")
                
                if 'solid_state' not in columns:
                    print(f"➕ 为表 {table_name} 添加 solid_state 字段")
                    cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN solid_state BOOLEAN DEFAULT FALSE")
                
                print(f"✅ 表 {table_name} 迁移完成")
    
    def create_customers_table(self):
        """创建客户表"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS customers (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT UNIQUE NOT NULL,
                    price REAL DEFAULT 0.0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            print("✅ 客户表创建完成")
    
    def generate_order_number(self, start_number: str, date: str = None) -> str:
        """根据开头数字生成订单编号"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 查找以该开头数字开头的所有订单编号
            cursor.execute(f"SELECT number FROM {table_name} WHERE number LIKE ? ORDER BY number", (f"{start_number}%",))
            existing_numbers = [row[0] for row in cursor.fetchall()]
            
            print(f"🔍 查找开头为 '{start_number}' 的现有编号: {existing_numbers}")
            
            # 提取数字部分并排序
            numbers = []
            for num_str in existing_numbers:
                try:
                    # 提取数字部分（去掉下划线后的内容）
                    if '_' in num_str:
                        num_part = num_str.split('_')[0]
                    else:
                        num_part = num_str  # 如果没有下划线，直接使用整个字符串
                    
                    # 只处理以start_number开头的编号
                    if num_part.startswith(start_number):
                        numbers.append(int(num_part))
                        print(f"  📊 提取编号: {num_part} -> {int(num_part)}")
                except ValueError:
                    continue
            
            print(f"📋 提取的数字列表: {numbers}")
            
            # 找到下一个可用的编号
            if not numbers:
                # 如果没有现有编号，从start_number + "01"开始
                next_number = int(start_number + "01")
                print(f"🆕 没有现有编号，从 {next_number} 开始")
            else:
                next_number = max(numbers) + 1
                print(f"🔄 最大编号: {max(numbers)}, 下一个: {next_number}")
            
            return str(next_number)
    
    def create_order(self, order_data: dict, date: str = None) -> dict:
        """创建订单"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            # 如果提供了start_number，则生成编号
            if 'start_number' in order_data:
                generated_number = self.generate_order_number(order_data['start_number'], date)
                order_data['number'] = generated_number
                print(f"🔢 生成订单编号: {generated_number} (开头: {order_data['start_number']})")
            
            cursor.execute(f"""
                INSERT INTO {table_name} (
                    number, customer, priority, notes, is_pending_engraving,
                    color, paper_direction, delivery_date, order_taker, designer, 
                    proofreader, proofreading_time, path, file_type
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                order_data['number'], order_data['customer'], order_data.get('priority', '中级'),
                order_data.get('notes', ''), order_data.get('is_pending_engraving', False),
                order_data.get('color', '单黑'), order_data.get('paper_direction', '上下'),
                order_data.get('delivery_date', '当天'), order_data.get('order_taker', ''),
                order_data.get('designer', ''), order_data.get('proofreader', ''),
                order_data.get('proofreading_time', ''), order_data.get('path', ''), order_data.get('file_type', 'PDF')
            ))
            
            conn.commit()
            return self.get_order_by_number(order_data['number'], date)
    
    def get_order_by_number(self, order_number: str, date: str = None) -> Optional[dict]:
        """通过编号获取订单"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute(f"SELECT * FROM {table_name} WHERE number = ?", (order_number,))
            row = cursor.fetchone()
            
            if row:
                return dict(row)
            return None
    
    def get_all_orders(self, date: str = None) -> List[dict]:
        """获取所有订单"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute(f"SELECT * FROM {table_name} ORDER BY created_at DESC")
            rows = cursor.fetchall()
            
            return [dict(row) for row in rows]
    
    def update_order(self, order_number: str, order_data: dict, date: str = None) -> Optional[dict]:
        """更新订单"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 构建更新语句
            set_clauses = []
            values = []
            
            for key, value in order_data.items():
                # 排除不能存储到数据库的字段
                if key not in ['number', 'status_data']:
                    set_clauses.append(f"{key} = ?")
                    values.append(value)
            
            if not set_clauses:
                return self.get_order_by_number(order_number, date)
            
            values.append(order_number)
            set_clauses.append("updated_at = CURRENT_TIMESTAMP")
            
            cursor.execute(f"""
                UPDATE {table_name} 
                SET {', '.join(set_clauses)}
                WHERE number = ?
            """, values)
            
            conn.commit()
            return self.get_order_by_number(order_number, date)
    
    def delete_order(self, order_number: str, date: str = None) -> bool:
        """删除订单"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 删除订单
            cursor.execute(f"DELETE FROM {table_name} WHERE number = ?", (order_number,))
            
            conn.commit()
            return cursor.rowcount > 0
    
    
    def find_order_by_number(self, number: str, date: str = None) -> Optional[dict]:
        """根据订单号查找订单"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute(f"SELECT * FROM {table_name} WHERE number = ?", (number,))
            row = cursor.fetchone()
            
            if row:
                return dict(row)
            return None
    
    def get_order_with_status(self, order_number: str, status_data: dict, date: str = None) -> Optional[dict]:
        """获取订单并添加状态信息"""
        order = self.get_order_by_number(order_number, date)
        if order:
            order['status_data'] = status_data
        return order
    
    def get_orders_by_customer(self, customer: str, date: str = None) -> List[dict]:
        """根据客户名称获取订单"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute(f"SELECT * FROM {table_name} WHERE customer LIKE ? ORDER BY created_at DESC", 
                         (f"%{customer}%",))
            rows = cursor.fetchall()
            
            return [dict(row) for row in rows]
    
    def get_orders_by_status(self, status_field: str, value: bool = True, date: str = None) -> List[dict]:
        """根据状态字段获取订单"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute(f"SELECT * FROM {table_name} WHERE {status_field} = ? ORDER BY created_at DESC", 
                         (value,))
            rows = cursor.fetchall()
            
            return [dict(row) for row in rows]
    
    def search_orders(self, keyword: str, date: str = None) -> List[dict]:
        """搜索订单"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute(f"""
                SELECT * FROM {table_name} 
                WHERE number LIKE ? OR customer LIKE ? OR notes LIKE ?
                ORDER BY created_at DESC
            """, (f"%{keyword}%", f"%{keyword}%", f"%{keyword}%"))
            rows = cursor.fetchall()
            
            return [dict(row) for row in rows]
    
    def get_statistics(self, date: str = None) -> dict:
        """获取统计信息"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 总订单数
            cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
            total_orders = cursor.fetchone()[0]
            
            # 已完成订单数
            cursor.execute(f"SELECT COUNT(*) FROM {table_name} WHERE is_completed = 1")
            completed_orders = cursor.fetchone()[0]
            
            # 待处理订单数
            cursor.execute(f"SELECT COUNT(*) FROM {table_name} WHERE is_completed = 0")
            pending_orders = cursor.fetchone()[0]
            
            # 今日订单数（对于当前日期的表，就是总订单数）
            today_orders = total_orders
            
            return {
                'total_orders': total_orders,
                'completed_orders': completed_orders,
                'pending_orders': pending_orders,
                'today_orders': today_orders,
                'completion_rate': (completed_orders / total_orders * 100) if total_orders > 0 else 0,
                'date': date
            }
    
    def get_all_dates(self) -> List[str]:
        """获取所有日期表"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 查询所有以orders_开头的表
            cursor.execute("""
                SELECT name FROM sqlite_master 
                WHERE type='table' AND name LIKE 'orders_%'
                ORDER BY name DESC
            """)
            
            tables = cursor.fetchall()
            dates = []
            for table in tables:
                table_name = table[0]
                if table_name.startswith('orders_'):
                    date_str = table_name[7:]  # 去掉 'orders_' 前缀
                    dates.append(date_str)
            
            return dates
    
    def delete_old_tables(self, keep_days: int = 30):
        """删除旧的日期表，保留最近N天"""
        import os
        from datetime import datetime, timedelta
        
        cutoff_date = datetime.now() - timedelta(days=keep_days)
        cutoff_str = cutoff_date.strftime("%Y%m%d")
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 获取所有日期表
            cursor.execute("""
                SELECT name FROM sqlite_master 
                WHERE type='table' AND name LIKE 'orders_%'
            """)
            
            tables = cursor.fetchall()
            deleted_count = 0
            
            for table in tables:
                table_name = table[0]
                if table_name.startswith('orders_'):
                    date_str = table_name[7:]  # 去掉 'orders_' 前缀
                    
                    # 如果日期早于截止日期，删除表
                    if date_str < cutoff_str:
                        cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
                        deleted_count += 1
                        print(f"🗑️ 删除旧表: {table_name}")
            
            conn.commit()
            print(f"✅ 删除了 {deleted_count} 个旧表")
    
    def get_table_info(self, date: str = None) -> dict:
        """获取指定日期表的信息"""
        if date is None:
            date = self.current_date
        
        table_name = self.get_table_name(date)
        self.create_daily_table(date)  # 确保表存在
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 获取表结构信息
            cursor.execute(f"PRAGMA table_info({table_name})")
            columns = cursor.fetchall()
            
            # 获取记录数
            cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
            record_count = cursor.fetchone()[0]
            
            # 获取表大小（近似）
            cursor.execute(f"SELECT COUNT(*) FROM sqlite_master WHERE name = ?", (table_name,))
            table_exists = cursor.fetchone()[0] > 0
            
            return {
                'table_name': table_name,
                'date': date,
                'columns': len(columns),
                'record_count': record_count,
                'exists': table_exists,
                'column_info': [{'name': col[1], 'type': col[2]} for col in columns]
            }
    
    # ==================== 客户管理方法 ====================
    
    def create_customer(self, customer_data: dict) -> dict:
        """创建客户"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute("""
                INSERT INTO customers (name, price)
                VALUES (?, ?)
            """, (
                customer_data['name'],
                customer_data.get('price', 0.0)
            ))
            
            conn.commit()
            customer_id = cursor.lastrowid
            
            return self.get_customer_by_id(customer_id)
    
    def get_customer_by_id(self, customer_id: int) -> Optional[dict]:
        """通过ID获取客户"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute("SELECT * FROM customers WHERE id = ?", (customer_id,))
            row = cursor.fetchone()
            
            return dict(row) if row else None
    
    def get_all_customers(self) -> List[dict]:
        """获取所有客户"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute("SELECT * FROM customers ORDER BY created_at")
            rows = cursor.fetchall()
            
            return [dict(row) for row in rows]
    
    def update_customer(self, customer_id: int, customer_data: dict) -> Optional[dict]:
        """更新客户"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 构建更新语句
            set_clauses = []
            values = []
            
            if 'name' in customer_data:
                set_clauses.append("name = ?")
                values.append(customer_data['name'])
            
            if 'price' in customer_data:
                set_clauses.append("price = ?")
                values.append(customer_data['price'])
            
            if not set_clauses:
                return self.get_customer_by_id(customer_id)
            
            values.append(customer_id)
            set_clauses.append("updated_at = CURRENT_TIMESTAMP")
            
            cursor.execute(f"""
                UPDATE customers 
                SET {', '.join(set_clauses)}
                WHERE id = ?
            """, values)
            
            conn.commit()
            return self.get_customer_by_id(customer_id)
    
    def delete_customer(self, customer_id: int) -> bool:
        """删除客户"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute("DELETE FROM customers WHERE id = ?", (customer_id,))
            
            conn.commit()
            return cursor.rowcount > 0
    
    def batch_create_customers(self, customers_data: List[dict]) -> List[dict]:
        """批量创建客户"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            created_customers = []
            
            for customer_data in customers_data:
                try:
                    cursor.execute("""
                        INSERT INTO customers (name, price)
                        VALUES (?, ?)
                    """, (
                        customer_data['name'],
                        customer_data.get('price', 0.0)
                    ))
                    
                    customer_id = cursor.lastrowid
                    created_customer = self.get_customer_by_id(customer_id)
                    if created_customer:
                        created_customers.append(created_customer)
                        
                except sqlite3.IntegrityError:
                    # 如果客户名称已存在，跳过
                    print(f"客户名称 '{customer_data['name']}' 已存在，跳过")
                    continue
                except Exception as e:
                    print(f"创建客户 '{customer_data['name']}' 失败: {e}")
                    continue
            
            conn.commit()
            return created_customers
